import pymysql
import pandas as pd

db=pymysql.connect(
    host='localhost',
    user='root',
    password='123456',
    db='test',
    charset='utf8')
cursor=db.cursor()

#创建表
def create_table(cursor,db):
    sql="""
        create table test(
    jobName varchar(100) primary key not null ,
    highMonthPay int not null ,
    publishDate varchar(100) not null ,
    lowMonthPay int,
    headCount int not null ,
    degreeName varchar(100) not null ,
    recName varchar(100) not null ,
    areaCodeName varchar(100) not null ,
    recTags varchar(100) not null ,
    major varchar(100) not null ,
    recProperty varchar(100) not null
                 );
            
    """
    try:#执行sql语句
        cursor.execute(sql)
        #提交
        db.commit()
    except:
        # 如果发生错误则回滚
        db.rollback()
        print("error")
#查询表数据
def select_table(key,value):
    sql=f"select * from test where {key} = \'{value}\';"

    try:
        data=cursor.execute(sql)
        df=pd.read_sql(sql,db)
        return df
    except:
        db.rollback()
        print("error")

#写入数据
def insert_table(cursor,db):
    sql="""
    insert into test
    (jobName,highMonthPay,publishDate,lowMonthPay,headCount,degreeName,recName,areaCodeName,recTags,major,recProperty) values (%s,%d,%s,%d,%d,%s,%s,%s,%s,%s,%s);
    """
    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()
        print("error")


print(select_table("jobName", "java"))